package com.jxtpro.utils;

import java.io.FileInputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.jfinal.kit.Ret;
import com.jfinal.plugin.activerecord.Db;

public class PoiExt {
	
	public static Ret ReadExcel(String filename, String sql,
            int ColCount) {
    	Workbook wookbook = null;
    	try {
    		 wookbook = new XSSFWorkbook(filename);
    		return buildExcel(wookbook,sql);
        } catch (Exception ex) {
        	try {
				wookbook = new HSSFWorkbook(new FileInputStream(filename));
			}catch (Exception e) {
				e.printStackTrace();
			}
        	return buildExcel(wookbook,sql);
        }
       
    }
	
	
	private static Ret buildExcel(Workbook wookbook,String sql){
		Ret r = Ret.fail();
		Sheet sheet = wookbook.getSheetAt(0); 
        int rows = sheet.getPhysicalNumberOfRows();
        if(rows <= 1){
        	return r.set("msg", "导入数据为空");
        }
        int cols = sheet.getRow(0).getPhysicalNumberOfCells(); 
        Object[][] paras = new Object[rows][cols];
        for (int i = 1; i < rows; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                int cells = row.getPhysicalNumberOfCells();
                for (int j = 0; j < cells+1; j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_FORMULA:
                        	 paras[i][j] = cell.getArrayFormulaRange();
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            paras[i][j] = cell.getNumericCellValue();
                            break;
                        case Cell.CELL_TYPE_STRING:
                            paras[i][j] = cell.getStringCellValue();
                            break;
                        default:
                            paras[i][j] = null;
                            break;
                        }
                    }
                }
            }
        }
        //去掉空
        int rs = rows - 1;
        Object[][] args = new Object[rs][cols];
        for (int i=1; i<paras.length; i++) {
			for (int j=0; j<paras[i].length; j++) {
				args[i-1][j] = paras[i][j];
			}
        }
        wookbook = null;
       return exceBatch(sql,args,r);
	}
	
	
	private static Ret  exceBatch(String sql,Object[][] paras,Ret r){
		 int[] ret = Db.batch(sql, paras, 100);
	        int s = 0, l = 0;
	        for (int i = 0; i < ret.length; i++)
	            if (ret[i] == 1)
	                s++;
	            else
	                l++;
	        r =  Ret.ok("msg", s).set("lost", l).set("count",  ret.length);
	        return r;
	}
}
